package com.Bill;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.conf.OAConfig;
import com.db.DBConnection;
import com.seeyon.client.CTPRestClient;
import com.seeyon.client.CTPServiceClientManager;
import org.apache.commons.httpclient.HttpClient;
import org.apache.commons.httpclient.methods.PostMethod;
import org.apache.commons.httpclient.methods.RequestEntity;
import org.apache.commons.httpclient.methods.StringRequestEntity;
import org.apache.commons.httpclient.params.HttpMethodParams;
import org.dom4j.Document;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

/**
 * @author Guoan
 * @date 2024/10/16 - 12:50
 */
public class BillMainDal {
    private String ip="http://10.9.112.170";

    public void tbUserBill(String userCode){
        Connection conn=new DBConnection().getDefConn();
        Statement stmt=null;
        try {
            stmt=conn.createStatement();
            String url="http://10.9.6.14:81/billfold/inner/v1/invoice/list";
            SimpleDateFormat sf=new SimpleDateFormat("yyyyMMddHHmmss");
//			String modifyDate=sf.format(new Date());
//			ResultSet res=stmt.executeQuery("select createdate from hrlog where type='发票信息同步'");
//			while (res.next()) {
//				modifyDate=sf.format(res.getDate(1));
//			}
//			stmt.executeUpdate("delete from hrlog where type='发票信息同步'");
//			stmt.executeUpdate("insert into hrlog(type,createdate) values ('发票信息同步',getdate())");


            JSONObject param=new JSONObject();
            param.put("version", "1.0");
            param.put("nonce", new Date().getTime());
            param.put("timestamp", sf.format(new Date()));
            JSONObject data=new JSONObject();
            data.put("userId", userCode);
            data.put("reimbursementStatus", "0");
            data.put("size", "100");
            param.put("data", data.toJSONString().replaceAll("\"", "\\\""));

            HttpClient httpclient = new HttpClient();
            PostMethod method = new PostMethod(url);
            method.getParams().setParameter(HttpMethodParams.HTTP_CONTENT_CHARSET,"utf-8");
            method.setRequestHeader("Content-Type","application/json;charset=UTF-8");
            RequestEntity se=new StringRequestEntity(param.toJSONString(),"application/json" ,"UTF-8");
            method.setRequestEntity(se);
            httpclient.executeMethod(method);
            JSONObject obj = JSONObject.parseObject(method.getResponseBodyAsString());

            CTPServiceClientManager clientManager = CTPServiceClientManager.getInstance(OAConfig.rest_ip);

            CTPRestClient client = clientManager.getRestClient();
            client.authenticate(OAConfig.rest_user, OAConfig.rest_pw);

            HashMap<String, String> map=new HashMap<String, String>();
            map.put("template1_jdcxstyfp","8828910654929716621");
            map.put("template3_tydzfp","-8393781134982626213");
            map.put("template3_escxstyfp","-147723167457721019");
            map.put("template13_qdzp_pufp","-8516037866594220649");
            map.put("template5 _hkkyjpxcd","-9018010787355407638");
            map.put("ylmzsf","9130765416932855210");
            map.put("ghjfsrpj","961999285307785631");
            map.put("template7_czcdcfp","-8619797417291133098");
            map.put("template1_zzszyfp","-3590849780715666590");
            map.put("template1_zzsdzzyfp","-7607217821669899045");
            map.put("template2_zzsdzpufptxf","8691271132346404696");
            map.put("template2_zzsptfpdz","-2177024964106040201");
            map.put("template8_txf","1798627781992136878");
            map.put("template13_qdzp_zyfp","1672597103010991009");
            map.put("template2_zzsptfp","4377169692631843374");
            map.put("template6_kyfp","-7434065684522185445");
            map.put("template2_zzsptfpjs","7291312610223344288");
            map.put("template9_defp","-4933097012863940367");
            map.put("template12_qdp_pufp","-3352159344171072688");
            map.put("template4_hcp","2674856612350152331");
            map.put("fssrtypj","-8788971208442099252");
            map.put("shbxjpj","4338872093768761430");
            map.put("others","3239038616031543793");
            map.put("template3_tyjdfp","113359747307028531");
            map.put("template12_qdp_zyfp","4253780008688173548");
            map.put("fssrzypj","5639946572428758242");
            map.put("gysyjz","4928777874412487920");
            map.put("ylzysf","-6072550200266438148");
            map.put("zjwl","-2387447848506143853");
            map.put("shtthf","5651041469633743339");

            if(obj.getString("code").equals("200")){
                JSONArray list=obj.getJSONObject("data").getJSONArray("list");
                for (int i = 0; i < list.size(); i++) {
                    JSONObject bill=list.getJSONObject(i);

                    Element element,column,value;
                    Document doc= DocumentHelper.createDocument();
                    Element forms=doc.addElement("forms");
                    forms.addAttribute("version", "2.1");
                    Element root=forms.addElement("formExport");
                    element=root.addElement("summary");
                    element.addAttribute("id", "1309748970347515697");
                    element.addAttribute("name", "formmain_0466");
                    element=root.addElement("definitions");
                    element.addAttribute("valuetype", "10");
                    element.addAttribute("isnull", "true");
                    element.addAttribute("length", "0");
                    element=root.addElement("values");
                    try {
                        column=element.addElement("column");
                        column.addAttribute("name", "发票id");
                        value=column.addElement("value");
                        value.setText(nullToObject(bill.getString("id")));

                        column=element.addElement("column");
                        column.addAttribute("name", "发票代码");
                        value=column.addElement("value");
                        value.setText(nullToObject(bill.getString("invoiceCode")));

                        column=element.addElement("column");
                        column.addAttribute("name", "发票号");
                        value=column.addElement("value");
                        value.setText(nullToObject(bill.getString("invoiceNumber")));

                        column=element.addElement("column");
                        column.addAttribute("name", "发票类型");
                        value=column.addElement("value");
                        value.setText(nullToObject(map.get(bill.getString("invoiceCategory"))));

                        column=element.addElement("column");
                        column.addAttribute("name", "发票或票据");
                        value=column.addElement("value");
                        value.setText(nullToObject(bill.getString("invoiceOrBillOrder")));

                        column=element.addElement("column");
                        column.addAttribute("name", "开票时间");
                        value=column.addElement("value");
                        value.setText(nullToObject(bill.getString("invoiceTime")));

                        column=element.addElement("column");
                        column.addAttribute("name", "采集时间");
                        value=column.addElement("value");
                        value.setText(nullToObject(bill.getString("collectionTime")));

                        column=element.addElement("column");
                        column.addAttribute("name", "发票状态");
                        value=column.addElement("value");
                        value.setText(nullToObject(bill.getString("invoiceStatus")));

                        column=element.addElement("column");
                        column.addAttribute("name", "是否已查验");
                        value=column.addElement("value");
                        value.setText(nullToObject(bill.getString("checkFlag")));

                        column=element.addElement("column");
                        column.addAttribute("name", "报销状态");
                        value=column.addElement("value");
                        value.setText("4828030541132023061");

                        column=element.addElement("column");
                        column.addAttribute("name", "发票金额");
                        value=column.addElement("value");
                        value.setText(nullToObject(bill.getString("taxAmount")));

                        column=element.addElement("column");
                        column.addAttribute("name", "不含税金额");
                        value=column.addElement("value");
                        value.setText(nullToObject(bill.getString("taxFreeAmount")));

                        column=element.addElement("column");
                        column.addAttribute("name", "合计税额");
                        value=column.addElement("value");
                        value.setText(nullToObject(bill.getString("totalTax")));

                        column=element.addElement("column");
                        column.addAttribute("name", "机动车发票税率");
                        value=column.addElement("value");
                        value.setText(nullToObject(bill.getString("motorRate")));

                        column=element.addElement("column");
                        column.addAttribute("name", "备注");
                        value=column.addElement("value");
                        value.setText(nullToObject(bill.getString("memo")));

                        column=element.addElement("column");
                        column.addAttribute("name", "销方名称");
                        value=column.addElement("value");
                        value.setText(nullToObject(bill.getString("salerName")));

                        column=element.addElement("column");
                        column.addAttribute("name", "销方纳税人识别号");
                        value=column.addElement("value");
                        value.setText(nullToObject(bill.getString("salerTaxNo")));

                        column=element.addElement("column");
                        column.addAttribute("name", "销售方电话");
                        value=column.addElement("value");
                        value.setText(nullToObject(bill.getString("salerTelephone")));

                        column=element.addElement("column");
                        column.addAttribute("name", "销售方地址");
                        value=column.addElement("value");
                        value.setText(nullToObject(bill.getString("salerAddress")));

                        column=element.addElement("column");
                        column.addAttribute("name", "销售方开户行");
                        value=column.addElement("value");
                        value.setText(nullToObject(bill.getString("salerBankBranch")));

                        column=element.addElement("column");
                        column.addAttribute("name", "销售方账号");
                        value=column.addElement("value");
                        value.setText(nullToObject(bill.getString("salerBankCardNo")));

                        column=element.addElement("column");
                        column.addAttribute("name", "购方名称乘客姓名");
                        value=column.addElement("value");
                        value.setText(nullToObject(bill.getString("buyerName")));

                        column=element.addElement("column");
                        column.addAttribute("name", "购方纳税人识别号");
                        value=column.addElement("value");
                        value.setText(nullToObject(bill.getString("buyerTaxNo")));

                        column=element.addElement("column");
                        column.addAttribute("name", "购买方电话");
                        value=column.addElement("value");
                        value.setText(nullToObject(bill.getString("buyerTelephone")));

                        column=element.addElement("column");
                        column.addAttribute("name", "购买方地址");
                        value=column.addElement("value");
                        value.setText(nullToObject(bill.getString("buyerAddress")));

                        column=element.addElement("column");
                        column.addAttribute("name", "购买方开户行");
                        value=column.addElement("value");
                        value.setText(nullToObject(bill.getString("buyerBankBranch")));

                        column=element.addElement("column");
                        column.addAttribute("name", "购买方账号");
                        value=column.addElement("value");
                        value.setText(nullToObject(bill.getString("buyerBankCardNo")));

                        column=element.addElement("column");
                        column.addAttribute("name", "附件文本");
                        value=column.addElement("value");
                        value.setText(nullToObject(bill.getString("invoiceUrl")));

//						column=element.addElement("column");
//						column.addAttribute("name", "附件文本");
//						value=column.addElement("value");
//						value.setText(nullToObject(bill.getString("pdfUrl")));

                        column=element.addElement("column");
                        column.addAttribute("name", "校验码");
                        value=column.addElement("value");
                        value.setText(nullToObject(bill.getString("checkCode")));

                        column=element.addElement("column");
                        column.addAttribute("name", "发票所在地");
                        value=column.addElement("value");
                        value.setText(nullToObject(bill.getString("invoiceLocation")));

                        column=element.addElement("column");
                        column.addAttribute("name", "里程");
                        value=column.addElement("value");
                        value.setText(nullToObject(bill.getString("mileage")));

                        column=element.addElement("column");
                        column.addAttribute("name", "燃油附加费");
                        value=column.addElement("value");
                        value.setText(nullToObject(bill.getString("fuelSurcharge")));

                        column=element.addElement("column");
                        column.addAttribute("name", "其他税费");
                        value=column.addElement("value");
                        value.setText(nullToObject(bill.getString("otherTaxAmount")));

                        column=element.addElement("column");
                        column.addAttribute("name", "航班号");
                        value=column.addElement("value");
                        value.setText(nullToObject(bill.getString("flightNo")));

                        column=element.addElement("column");
                        column.addAttribute("name", "火车票Id");
                        value=column.addElement("value");
                        value.setText(nullToObject(bill.getString("ticketNo")));

                        column=element.addElement("column");
                        column.addAttribute("name", "火车票红色编码");
                        value=column.addElement("value");
                        value.setText(nullToObject(bill.getString("ticketCode")));

                        column=element.addElement("column");
                        column.addAttribute("name", "座位类别");
                        value=column.addElement("value");
                        value.setText(nullToObject(bill.getString("seatLevel")));

                        column=element.addElement("column");
                        column.addAttribute("name", "出发地");
                        value=column.addElement("value");
                        value.setText(nullToObject(bill.getString("departure")));

                        column=element.addElement("column");
                        column.addAttribute("name", "目的地");
                        value=column.addElement("value");
                        value.setText(nullToObject(bill.getString("destination")));

                        column=element.addElement("column");
                        column.addAttribute("name", "车次");
                        value=column.addElement("value");
                        value.setText(nullToObject(bill.getString("trainNo")));

                        column=element.addElement("column");
                        column.addAttribute("name", "座位号");
                        value=column.addElement("value");
                        value.setText(nullToObject(bill.getString("seatNo")));

                        column=element.addElement("column");
                        column.addAttribute("name", "检票口");
                        value=column.addElement("value");
                        value.setText(nullToObject(bill.getString("checkIn")));

                        column=element.addElement("column");
                        column.addAttribute("name", "上车时间");
                        value=column.addElement("value");
                        value.setText(nullToObject(bill.getString("boardingTime")));

                        column=element.addElement("column");
                        column.addAttribute("name", "下车时间");
                        value=column.addElement("value");
                        value.setText(nullToObject(bill.getString("alightingTime")));

                        column=element.addElement("column");
                        column.addAttribute("name", "车号");
                        value=column.addElement("value");
                        value.setText(nullToObject(bill.getString("carNo")));

                        column=element.addElement("column");
                        column.addAttribute("name", "填开单位");
                        value=column.addElement("value");
                        value.setText(nullToObject(bill.getString("invoiceCompany")));

                        column=element.addElement("column");
                        column.addAttribute("name", "填开日期");
                        value=column.addElement("value");
                        value.setText(nullToObject(bill.getString("invoiceDate")));

                        column=element.addElement("column");
                        column.addAttribute("name", "民航发展基金");
                        value=column.addElement("value");
                        value.setText(nullToObject(bill.getString("fundAmount")));

                        column=element.addElement("column");
                        column.addAttribute("name", "保险费");
                        value=column.addElement("value");
                        value.setText(nullToObject(bill.getString("premium")));

                        column=element.addElement("column");
                        column.addAttribute("name", "交款人名称");
                        value=column.addElement("value");
                        value.setText(nullToObject(bill.getString("payerPartyName")));

                        column=element.addElement("column");
                        column.addAttribute("name", "票面身份证号码");
                        value=column.addElement("value");
                        value.setText(nullToObject(bill.getString("payerPartyCode")));

                        column=element.addElement("column");
                        column.addAttribute("name", "交款人类型");
                        value=column.addElement("value");
                        value.setText(nullToObject(bill.getString("payerPartyType")));

                        column=element.addElement("column");
                        column.addAttribute("name", "开票单位全称");
                        value=column.addElement("value");
                        value.setText(nullToObject(bill.getString("invoicingPartyName")));

                        column=element.addElement("column");
                        column.addAttribute("name", "开票单位代码");
                        value=column.addElement("value");
                        value.setText(nullToObject(bill.getString("invoicingPartyCode")));

                        Element subForms=root.addElement("subForms");
                        Element subForm=subForms.addElement("subForm");
                        subForm.addElement("definitions");
                        element=subForm.addElement("values");
                        JSONArray detailVoList  = bill.getJSONArray("personalInvoiceDetailVoList");
                        for (int j = 0; j < detailVoList.size(); j++) {
                            JSONObject detail=detailVoList.getJSONObject(j);
                            Element row = element.addElement("row");

                            column=row.addElement("column");
                            column.addAttribute("name", "单据明细序号");
                            value=column.addElement("value");
                            value.setText(nullToObject(detail.getString("djmxxh")));

                            column=row.addElement("column");
                            column.addAttribute("name", "发票行性质");
                            value=column.addElement("value");
                            value.setText(nullToObject(detail.getString("fphxz")));

                            column=row.addElement("column");
                            column.addAttribute("name", "商品名称");
                            value=column.addElement("value");
                            value.setText(nullToObject(detail.getString("goodsName")));

                            column=row.addElement("column");
                            column.addAttribute("name", "数量");
                            value=column.addElement("value");
                            value.setText(nullToObject(detail.getString("num")));

                            column=row.addElement("column");
                            column.addAttribute("name", "单价");
                            value=column.addElement("value");
                            value.setText(nullToObject(detail.getString("price")));

                            column=row.addElement("column");
                            column.addAttribute("name", "税率");
                            value=column.addElement("value");
                            value.setText(nullToObject(detail.getString("taxRate")));

                            column=row.addElement("column");
                            column.addAttribute("name", "规格型号");
                            value=column.addElement("value");
                            value.setText(nullToObject(detail.getString("spec")));

                            column=row.addElement("column");
                            column.addAttribute("name", "单位");
                            value=column.addElement("value");
                            value.setText(nullToObject(detail.getString("unit")));

                            column=row.addElement("column");
                            column.addAttribute("name", "商品编码");
                            value=column.addElement("value");
                            value.setText(nullToObject(detail.getString("spbm")));

                            column=row.addElement("column");
                            column.addAttribute("name", "优惠政策标识");
                            value=column.addElement("value");
                            value.setText(nullToObject(detail.getString("yhzcbs")));

                            column=row.addElement("column");
                            column.addAttribute("name", "普通零税率");
                            value=column.addElement("value");
                            value.setText(nullToObject(detail.getString("lslbs")));

                            column=row.addElement("column");
                            column.addAttribute("name", "增值税特殊管理");
                            value=column.addElement("value");
                            value.setText(nullToObject(detail.getString("zzstsgl")));

                            column=row.addElement("column");
                            column.addAttribute("name", "自行编码");
                            value=column.addElement("value");
                            value.setText(nullToObject(detail.getString("zxbm")));

                            column=row.addElement("column");
                            column.addAttribute("name", "扣除额");
                            value=column.addElement("value");
                            value.setText(nullToObject(detail.getString("kce")));

                            column=row.addElement("column");
                            column.addAttribute("name", "税额");
                            value=column.addElement("value");
                            value.setText(nullToObject(detail.getString("tax")));

                            column=row.addElement("column");
                            column.addAttribute("name", "不含税金额");
                            value=column.addElement("value");
                            value.setText(nullToObject(detail.getString("taxFreeAmount")));

                            column=row.addElement("column");
                            column.addAttribute("name", "含税金额");
                            value=column.addElement("value");
                            value.setText(nullToObject(detail.getString("taxAmount")));

                            column=row.addElement("column");
                            column.addAttribute("name", "不含税单价");
                            value=column.addElement("value");
                            value.setText(nullToObject(detail.getString("taxFreePrice")));

                            column=row.addElement("column");
                            column.addAttribute("name", "商品编码简称");
                            value=column.addElement("value");
                            value.setText(nullToObject(detail.getString("spbm_abb")));

                            column=row.addElement("column");
                            column.addAttribute("name", "归集时间");
                            value=column.addElement("value");
                            value.setText(nullToObject(detail.getString("collectionTime")));
                        }

                        Map para = new HashMap();
                        para.put("templateCode", "pjxx01");
                        para.put("loginName", "yysjs");
                        para.put("dataXml", doc.asXML());

                        String result=client.post("form/import/pjxx01",para, String.class);
                    } catch (Exception e) {
                        e.printStackTrace();
                        // TODO: handle exception
                    }

                }
            }
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }
    }

    public void getUserBill(String userCode){
        Connection conn=new DBConnection().getDefConn();
        Statement stmt=null;
        try {
            stmt=conn.createStatement();
            //获取票夹数据接口地址
            String url=ip+"/billfold/inner/v1/invoice/list";
            SimpleDateFormat sf=new SimpleDateFormat("yyyyMMddHHmmss");

            HashMap<String, String> map=new HashMap<String, String>();
            map.put("template1_jdcxstyfp","8828910654929716621");
            map.put("template3_tydzfp","-8393781134982626213");
            map.put("template3_escxstyfp","-147723167457721019");
            map.put("template13_qdzp_pufp","-8516037866594220649");
            map.put("template5 _hkkyjpxcd","-9018010787355407638");
            map.put("ylmzsf","9130765416932855210");
            map.put("ghjfsrpj","961999285307785631");
            map.put("template7_czcdcfp","-8619797417291133098");
            map.put("template1_zzszyfp","-3590849780715666590");
            map.put("template1_zzsdzzyfp","-7607217821669899045");
            map.put("template2_zzsdzpufptxf","8691271132346404696");
            map.put("template2_zzsptfpdz","-2177024964106040201");
            map.put("template8_txf","1798627781992136878");
            map.put("template13_qdzp_zyfp","1672597103010991009");
            map.put("template2_zzsptfp","4377169692631843374");
            map.put("template6_kyfp","-7434065684522185445");
            map.put("template2_zzsptfpjs","7291312610223344288");
            map.put("template9_defp","-4933097012863940367");
            map.put("template12_qdp_pufp","-3352159344171072688");
            map.put("template4_hcp","2674856612350152331");
            map.put("fssrtypj","-8788971208442099252");
            map.put("shbxjpj","4338872093768761430");
            map.put("others","3239038616031543793");
            map.put("template3_tyjdfp","113359747307028531");
            map.put("template12_qdp_zyfp","4253780008688173548");
            map.put("fssrzypj","5639946572428758242");
            map.put("gysyjz","4928777874412487920");
            map.put("ylzysf","-6072550200266438148");
            map.put("zjwl","-2387447848506143853");
            map.put("shtthf","5651041469633743339");

            //删除a_sew_bill表 usercode = usercode
            stmt.executeUpdate("delete from a_sew_bill where usercode='"+userCode+"'");
            int page=1;
            int pageSize=100;
            StringBuffer insert = new StringBuffer();
            //循环100次
            while (pageSize==100) {
                pageSize=0;

                //组装查询参数
                JSONObject param=new JSONObject();
                param.put("version", "1.0");
                param.put("nonce", new Date().getTime());
                param.put("timestamp", sf.format(new Date()));
                JSONObject data=new JSONObject();
                data.put("userId", userCode);
                data.put("reimbursementStatus", "0");
                data.put("size", "100");
                data.put("page", page);
                param.put("data", data.toJSONString().replaceAll("\"", "\\\""));

                //调通接口查询
                HttpClient httpclient = new HttpClient();
                PostMethod method = new PostMethod(url);
                method.getParams().setParameter(HttpMethodParams.HTTP_CONTENT_CHARSET,"utf-8");
                method.setRequestHeader("Content-Type","application/json;charset=UTF-8");
                RequestEntity se=new StringRequestEntity(param.toJSONString(),"application/json" ,"UTF-8");
                method.setRequestEntity(se);
                httpclient.executeMethod(method);
                JSONObject obj = JSONObject.parseObject(method.getResponseBodyAsString());
                //接口返回成功之后，获取返回数据
                if(obj.getString("code").equals("200")){
                    JSONArray list=obj.getJSONObject("data").getJSONArray("list");
                    pageSize=list.size();
                    //循环返回数据，将数据插入到a_sew_bill表
                    for (int i = 0; i < list.size(); i++) {
                        JSONObject bill=list.getJSONObject(i);
                        if(nullToObject(bill.getString("isReimbursed")).equals("true"))
                            continue;
                        insert.append("insert into a_sew_bill(" +
                                "usercode" +
                                ",id"+
                                ",invoiceCode"+
                                ",invoiceNumber"+
                                ",invoiceCategory"+
                                ",invoiceOrBillOrder"+
                                ",invoiceTime"+
                                ",collectionTime"+
                                ",invoiceStatus"+
                                ",checkFlag"+
                                ",reimbursementStatus"+
                                ",taxAmount"+
                                ",taxFreeAmount"+
                                ",totalTax"+
                                ",motorRate"+
                                ",memo"+
                                ",salerName"+
                                ",salerTaxNo"+
                                ",salerTelephone"+
                                ",salerAddress"+
                                ",salerBankBranch"+
                                ",salerBankCardNo"+
                                ",buyerName"+
                                ",buyerTaxNo"+
                                ",buyerTelephone"+
                                ",buyerAddress"+
                                ",buyerBankBranch"+
                                ",buyerBankCardNo"+
                                ",invoiceUrl"+
                                ",pdfUrl"+
                                ",checkCode"+
                                ",invoiceLocation"+
                                ",mileage"+
                                ",fuelSurcharge"+
                                ",otherTaxAmount"+
                                ",flightNo"+
                                ",ticketNo"+
                                ",ticketCode"+
                                ",seatLevel"+
                                ",departure"+
                                ",destination"+
                                ",trainNo"+
                                ",seatNo"+
                                ",checkIn"+
                                ",boardingTime"+
                                ",alightingTime"+
                                ",carNo"+
                                ",invoiceCompany"+
                                ",invoiceDate"+
                                ",fundAmount"+
                                ",premium"+
                                ",payerPartyName"+
                                ",payerPartyCode"+
                                ",payerPartyType"+
                                ",invoicingPartyName"+
                                ",invoicingPartyCode," +
                                "editFlag"+
                                ") values (" +
                                "'"+userCode+"'" +
                                ",'"+nullToObject(bill.getString("id"))+"'"+
                                ",'"+nullToObject(bill.getString("invoiceCode"))+"'"+
                                ",'"+nullToObject(bill.getString("invoiceNumber"))+"'"+
                                ",'"+nullToObject(bill.getString("invoiceCategory"))+"'"+
                                ",'"+nullToObject(bill.getString("invoiceOrBillOrder"))+"'"+
                                ",'"+nullToObject(bill.getString("invoiceTime"))+"'"+
                                ",'"+nullToObject(bill.getString("collectionTime"))+"'"+
                                ",'"+nullToObject(bill.getString("invoiceStatus"))+"'"+
                                ",'"+nullToObject(bill.getString("checkFlag"))+"'"+
                                ",'"+nullToObject(bill.getString("reimbursementStatus"))+"'"+
                                ",'"+nullToObject(bill.getString("taxAmount"))+"'"+
                                ",'"+nullToObject(bill.getString("taxFreeAmount"))+"'"+
                                ",'"+nullToObject(bill.getString("totalTax"))+"'"+
                                ",'"+nullToObject(bill.getString("motorRate"))+"'"+
                                ",'"+nullToObject(bill.getString("memo"))+"'"+
                                ",'"+nullToObject(bill.getString("salerName"))+"'"+
                                ",'"+nullToObject(bill.getString("salerTaxNo"))+"'"+
                                ",'"+nullToObject(bill.getString("salerTelephone"))+"'"+
                                ",'"+nullToObject(bill.getString("salerAddress"))+"'"+
                                ",'"+nullToObject(bill.getString("salerBankBranch"))+"'"+
                                ",'"+nullToObject(bill.getString("salerBankCardNo"))+"'"+
                                ",'"+nullToObject(bill.getString("buyerName"))+"'"+
                                ",'"+nullToObject(bill.getString("buyerTaxNo"))+"'"+
                                ",'"+nullToObject(bill.getString("buyerTelephone"))+"'"+
                                ",'"+nullToObject(bill.getString("buyerAddress"))+"'"+
                                ",'"+nullToObject(bill.getString("buyerBankBranch"))+"'"+
                                ",'"+nullToObject(bill.getString("buyerBankCardNo"))+"'"+
                                ",'"+nullToObject(bill.getString("invoiceUrl"))+"'"+
                                ",'"+nullToObject(bill.getString("pdfUrl"))+"'"+
                                ",'"+nullToObject(bill.getString("checkCode"))+"'"+
                                ",'"+nullToObject(bill.getString("invoiceLocation"))+"'"+
                                ",'"+nullToObject(bill.getString("mileage"))+"'"+
                                ",'"+nullToObject(bill.getString("fuelSurcharge"))+"'"+
                                ",'"+nullToObject(bill.getString("otherTaxAmount"))+"'"+
                                ",'"+nullToObject(bill.getString("flightNo"))+"'"+
                                ",'"+nullToObject(bill.getString("ticketNo"))+"'"+
                                ",'"+nullToObject(bill.getString("ticketCode"))+"'"+
                                ",'"+nullToObject(bill.getString("seatLevel"))+"'"+
                                ",'"+nullToObject(bill.getString("departure"))+"'"+
                                ",'"+nullToObject(bill.getString("destination"))+"'"+
                                ",'"+nullToObject(bill.getString("trainNo"))+"'"+
                                ",'"+nullToObject(bill.getString("seatNo"))+"'"+
                                ",'"+nullToObject(bill.getString("checkIn"))+"'"+
                                ",'"+nullToObject(bill.getString("boardingTime"))+"'"+
                                ",'"+nullToObject(bill.getString("alightingTime"))+"'"+
                                ",'"+nullToObject(bill.getString("carNo"))+"'"+
                                ",'"+nullToObject(bill.getString("invoiceCompany"))+"'"+
                                ",'"+nullToObject(bill.getString("invoiceDate"))+"'"+
                                ",'"+nullToObject(bill.getString("fundAmount"))+"'"+
                                ",'"+nullToObject(bill.getString("premium"))+"'"+
                                ",'"+nullToObject(bill.getString("payerPartyName"))+"'"+
                                ",'"+nullToObject(bill.getString("payerPartyCode"))+"'"+
                                ",'"+nullToObject(bill.getString("payerPartyType"))+"'"+
                                ",'"+nullToObject(bill.getString("invoicingPartyName"))+"'"+
                                ",'"+nullToObject(bill.getString("invoicingPartyCode"))+"'"+
                                ",'"+nullToObject(bill.getString("editFlag"))+"'"+
                                ");");
                    }
                }
                page++;
            }
            if(!insert.toString().equals("")){
                try {
                    System.out.println(insert);
                    stmt.executeUpdate(insert.toString());
                } catch (Exception e) {
                    // TODO: handle exception
                    e.printStackTrace();
                }
            }

        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }
    }

//    public void tbBillState(){
//        System.out.println("无参同步方法-----------------------------------------------------------");
//        //创建JDBC连接
//        Connection conn=new DBConnection().getDefConn();
//        Statement stmt=null;
//        try {//20736
//            stmt=conn.createStatement();
//            SimpleDateFormat sf=new SimpleDateFormat("yyyyMMddHHmmss");
//            SimpleDateFormat sf2=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
//            //将当前时间转为字符串格式
//            String modifyDate=sf2.format(new Date());
//            //查询hrlog表中type=“发票状态更新” 的创建时间
//            ResultSet res=stmt.executeQuery("select createdate from hrlog where type='发票状态更新'");
//            while (res.next()) {
//                modifyDate=res.getString(1);
//            }
//            //删除hrlog表type=“发票状态更新”
//            stmt.executeUpdate("delete from hrlog where type='发票状态更新'");
//            //创建htlog表type=“发票状态更新” 数据
//            stmt.executeUpdate("insert into hrlog(type,createdate) values ('发票状态更新',getdate())");
//
//            //出差
//            //内连接org_unit的id = field0321 col_summary(协同代办表).form_recordid(数据id) = formmain_0706(数据表).id ctp_affair(个人代办表).object_id = col_summary.id
//            //ctp_affair =1 和ctp_affair.update_date >= hrlog表中的创建时间
//            res=stmt.executeQuery("select m.id,field0059 员工工号,field0451 税号,u.name 所属单位,field0057 单据编号,s.state 状态," +
//                    "a.state 待办状态,a.sub_state 处理状态 " +
//                    " from formmain_0706 m,org_unit u,col_summary s,ctp_affair a " +
//                    " where u.id=field0321 " +
//                    " and s.form_recordid=m.id " +// and s.form_appid=7460453458957979234
//                    " and a.object_id = s.id and a.app=1 " +
//                    " and a.update_date>='"+modifyDate+"' order by a.update_date ");
//            JSONArray dataList=new JSONArray();
//            //循环获取字段
//            while (res.next()) {
//                JSONObject obj=new JSONObject();
//                obj.put("userId", res.getString("员工工号"));//用户编码
//                obj.put("agencyCode", res.getString("税号"));//报销单位税号
//                obj.put("agencyName", res.getString("所属单位"));//报销单位名称
//                obj.put("reimbursementId", res.getString("单据编号"));//报销单号
//                //当个人代办表中的待办状态 不等于 null 并且 待办状态 = 1 并且（处理状态 =3 或者 处理状态 = 16 或者 处理状态 = 18） reimbursementStatus = 0 报销中
////                if(res.getString("待办状态")!=null && res.getString("待办状态").equals("1")
////                        && (res.getString("处理状态").equals("3") || res.getString("处理状态").equals("16") || res.getString("处理状态").equals("18"))
////                ) {
////                    obj.put("reimbursementStatus", "0");//报销中
////                }else
////                {
////                    //当协同表中的状态 不等于 null 并且 状态 = 3   reimbursementStatus = 2 已报销
////                    //当协同表中的状态 不等于 null 并且 状态 = 0   reimbursementStatus = 1 报销中
////                    //当以上条件都不满足时  reimbursementStatus = 0 报销中
////                    if(res.getString("状态")!=null && res.getString("状态").equals("3")) {
////                        obj.put("reimbursementStatus", "2");//已报销
////                    }else if(res.getString("状态")!=null && res.getString("状态").equals("0")) {
////                        obj.put("reimbursementStatus", "1");//报销中
////                    }else {
////                        obj.put("reimbursementStatus", "0");//未报销
////                    }
////                }
//                if(res.getString("待办状态")!=null && (res.getString("待办状态").equals("3") || res.getString("待办状态").equals("4"))
//                        && (res.getString("处理状态").equals("0") || res.getString("处理状态").equals("11"))){
//                    if(res.getString("状态")!=null && res.getString("状态").equals("0")){
//                        obj.put("reimbursementStatus", "1");//报销中
//                    }
//                }
//                obj.put("flowCode", nullToObject(res.getString("单据编号")));//报销单号
//                obj.put("id", res.getString("id"));
//                dataList.add(obj);
//            }
//            //循环获取到的字段数组
//            for (int i = 0; i < dataList.size(); i++) {
//                //查询 formson_0709 表中主表id字段 = 获取到的id 查询 field0432 连接 formson_0710 表中主表id字段 = id 查询 field0007
//                // 连接 formson_0711 表中主表id字段 = 获取到的id 查询  field0433
//                res = stmt.executeQuery("select field0432 as invoiceId,'a' from formson_0709 where formmain_id = "+dataList.getJSONObject(i).getString("id")+" " +
//                        " union select field0007 as invoiceId,'b' from formson_0710 where formmain_id = "+dataList.getJSONObject(i).getString("id")+" " +
//                        " union select field0433 as invoiceId,'c' from formson_0711 where formmain_id = "+dataList.getJSONObject(i).getString("id"));
//                JSONArray invoiceList=new JSONArray();
//
//                while (res.next()) {
//                    if(res.getString("invoiceId")!=null){
//                        JSONObject obj=new JSONObject();
//                        obj.put("invoiceId", res.getString(1));
//                        invoiceList.add(obj);
//                    }
//                }
//                //将所有获取到的invoiceId 放入dataList数组中
//                dataList.getJSONObject(i).put("invoiceList", invoiceList);
//
//                //金税票夹系统接口地址
//                String url=ip+"/billfold/inner/v1/invoice/operateReimbursementStatus";
//                //组装参数
//                JSONObject param=new JSONObject();
//                param.put("version", "1.0");
//                param.put("nonce", new Date().getTime());
//                param.put("timestamp", sf.format(new Date()));
//                param.put("data", dataList.getJSONObject(i).toJSONString().replaceAll("\"", "\\\""));
//
//                //请求接口 更新发票报销状态
//                HttpClient httpclient = new HttpClient();
//                PostMethod method = new PostMethod(url);
//                method.getParams().setParameter(HttpMethodParams.HTTP_CONTENT_CHARSET,"utf-8");
//                method.setRequestHeader("Content-Type","application/json;charset=UTF-8");
//                RequestEntity se=new StringRequestEntity(param.toJSONString(),"application/json" ,"UTF-8");
//                method.setRequestEntity(se);
//                httpclient.executeMethod(method);
//                JSONObject obj = JSONObject.parseObject(method.getResponseBodyAsString());
//                System.out.println("formmain_0706单据编号:"+dataList.getJSONObject(i).getString("flowCode")+",状态:"+dataList.getJSONObject(i).getString("reimbursementStatus")+",结果:"+obj);
//            }
//
//            //现金报销
//            //查询 formmain_0722.id 连接 org_unit.id = formmain_0722.field0070  连接 col_summary(协同代办表).form_recordid(数据id)
//            //连接 ctp_affair(个人代办事项).object_id = col_summary.id  and ctp_affair.app = 1
//            //and ctp_affair.update_date >= hrlog表中的创建时间
//            res=stmt.executeQuery("select m.id,field0068 员工工号,field0227 税号,u.name 所属单位,field0001 单据编号,s.state 状态," +
//                    "a.state 待办状态,a.sub_state 处理状态 " +
//                    " from formmain_0722 m,org_unit u,col_summary s,ctp_affair a " +
//                    " where u.id=field0070 " +
//                    " and s.form_recordid=m.id " +
//                    " and a.object_id = s.id and a.app=1 " +
//                    " and a.update_date>='"+modifyDate+"' order by a.update_date ");
//            System.out.println("无参现金报销查询到的信息{}sql  select m.id,field0068 员工工号,field0227 税号,u.name 所属单位,field0001 单据编号,s.state 状态," +
//                    "a.state 待办状态,a.sub_state 处理状态 " +
//                    " from formmain_0722 m,org_unit u,col_summary s,ctp_affair a " +
//                    " where u.id=field0070 " +
//                    " and s.form_recordid=m.id " +
//                    " and a.object_id = s.id and a.app=1 " +
//                    " and a.update_date>='"+modifyDate+"' order by a.update_date ");
//            dataList=new JSONArray();
//            //循环查询回来的数据
//            while (res.next()) {
//                JSONObject obj=new JSONObject();
//                obj.put("userId", res.getString("员工工号"));//用户编码
//                obj.put("agencyCode", res.getString("税号"));//报销单位税号
//                obj.put("agencyName", res.getString("所属单位"));//报销单位名称
//                obj.put("reimbursementId", res.getString("单据编号"));//报销单号
//                System.out.println("待办状态{}" + res.getString("待办状态") + "处理状态{}" + res.getString("处理状态") + "状态{}" + res.getString("状态"));
//                //当ctp_affair的代办状态 不等于 null 并且 当ctp_affair的代办状态 = 1 并且 （处理状态  = 3 或者 处理状态 = 16 或者 处理状态 = 18）
//                //reimbursementStatus = 0
////                if(res.getString("待办状态")!=null && res.getString("待办状态").equals("1")
////                        && (res.getString("处理状态").equals("3") || res.getString("处理状态").equals("16") || res.getString("处理状态").equals("18"))
////                ) {
////                    System.out.println("无参现金报销中进来啦!--------");
////                    obj.put("reimbursementStatus", "0");//报销中
////                }else
////                {
////                    //col_summary的状态 不等于 null  并且  状态 = 3 reimbursementStatus =2 已报销
////                    //col_summary的状态 不等于 null  并且  状态 = 3 reimbursementStatus =0 报销中
////                    //当以上条件都不满足时  reimbursementStatus = 0 报销中
////                    if(res.getString("状态")!=null && res.getString("状态").equals("3")) {
////                        System.out.println("无参现金已报销进来啦!---------");
////                        obj.put("reimbursementStatus", "2");//已报销
////                    }else if(res.getString("状态")!=null && res.getString("状态").equals("0") && !res.getString("待办状态").equals("6")) {
////                        System.out.println("无参现金报销中进来啦!---------");
////                        obj.put("reimbursementStatus", "1");//报销中
////                    }else {
////                        System.out.println("无参现金未报销进来啦!---------");
////                        obj.put("reimbursementStatus", "0");//未报销
////                    }
////                }
//                if(res.getString("待办状态")!=null && (res.getString("待办状态").equals("3") || res.getString("待办状态").equals("4"))
//                && (res.getString("处理状态").equals("0") || res.getString("处理状态").equals("11"))){
//                    if(res.getString("状态")!=null && res.getString("状态").equals("0")){
//                        obj.put("reimbursementStatus", "1");//报销中
//                    }
//                }
//                obj.put("flowCode", nullToObject(res.getString("单据编号")));//报销单号
//                obj.put("id", res.getString("id"));
//                dataList.add(obj);
//            }
//            //循环获取到的字段数组
//            for (int i = 0; i < dataList.size(); i++) {
//                //根据formson_0723的主表字段id = id 查询 formson_0723表的field0192字段
//                res = stmt.executeQuery("select field0192 as invoiceId,'0723' from formson_0723 where formmain_id = "+dataList.getJSONObject(i).getString("id"));
//                System.out.println("循环现金报销查询到的数据{}select field0192 as invoiceId,'0723' from formson_0723 where formmain_id = "+dataList.getJSONObject(i).getString("id"));
//                JSONArray invoiceList=new JSONArray();
//
//                while (res.next()) {
//                    if(res.getString("invoiceId")!=null){
//                        JSONObject obj=new JSONObject();
//                        obj.put("invoiceId", res.getString(1));
//                        invoiceList.add(obj);
//                    }
//                }
//                dataList.getJSONObject(i).put("invoiceList", invoiceList);
//
//                //金税票夹系统接口地址
//                String url=ip+"/billfold/inner/v1/invoice/operateReimbursementStatus";
//                JSONObject param=new JSONObject();
//                param.put("version", "1.0");
//                param.put("nonce", new Date().getTime());
//                param.put("timestamp", sf.format(new Date()));
//                param.put("data", dataList.getJSONObject(i).toJSONString().replaceAll("\"", "\\\""));
//
//                //请求接口 更新发票报销状态
//                HttpClient httpclient = new HttpClient();
//                PostMethod method = new PostMethod(url);
//                method.getParams().setParameter(HttpMethodParams.HTTP_CONTENT_CHARSET,"utf-8");
//                method.setRequestHeader("Content-Type","application/json;charset=UTF-8");
//                RequestEntity se=new StringRequestEntity(param.toJSONString(),"application/json" ,"UTF-8");
//                method.setRequestEntity(se);
//                httpclient.executeMethod(method);
//                JSONObject obj = JSONObject.parseObject(method.getResponseBodyAsString());
//                System.out.println("formmain_0722单据编号:"+dataList.getJSONObject(i).getString("flowCode")+",状态:"+dataList.getJSONObject(i).getString("reimbursementStatus")+",结果:"+obj);
//            }
//
//            //用款报销
//            //查询formmain_0728 连接  org_unit.id = formmain_0728.field0070 连接 col_summary(协同表).form_recordid = formmain_0728.id
//            //ctp_affair(个人代办表).object_id =col_summary.id and  ctp_affair = 1 and ctp_affai.update_date >= hrlog表中的创建时间
//            res=stmt.executeQuery("select m.id,field0068 员工工号,field0119 税号,u.name 所属单位,field0001 单据编号,s.state 状态," +
//                    "a.state 待办状态,a.sub_state 处理状态 " +
//                    " from formmain_0728 m,org_unit u,col_summary s,ctp_affair a " +
//                    " where u.id=field0070 " +
//                    " and s.form_recordid=m.id " +
//                    " and a.object_id = s.id and a.app=1 " +
//                    " and a.update_date>='"+modifyDate+"' order by a.update_date ");
//            dataList=new JSONArray();
//            //循环sql查到的数据
//            while (res.next()) {
//                JSONObject obj=new JSONObject();
//                obj.put("userId", res.getString("员工工号"));//用户编码
//                obj.put("agencyCode", res.getString("税号"));//报销单位税号
//                obj.put("agencyName", res.getString("所属单位"));//报销单位名称
//                obj.put("reimbursementId", res.getString("单据编号"));//报销单号
////                if(res.getString("待办状态")!=null && res.getString("待办状态").equals("1")
////                        && (res.getString("处理状态").equals("3") || res.getString("处理状态").equals("16") || res.getString("处理状态").equals("18"))
////                ) {
////                    obj.put("reimbursementStatus", "0");//报销中
////                }else
////                {
////                    if(res.getString("状态")!=null && res.getString("状态").equals("3")) {
////                        obj.put("reimbursementStatus", "2");//已报销
////                    }else if(res.getString("状态")!=null && res.getString("状态").equals("0")) {
////                        obj.put("reimbursementStatus", "1");//报销中
////                    }else{
////                        obj.put("reimbursementStatus", "0");//未报销
////                    }
////                }
//                if(res.getString("待办状态")!=null && (res.getString("待办状态").equals("3") || res.getString("待办状态").equals("4"))
//                        && (res.getString("处理状态").equals("0") || res.getString("处理状态").equals("11"))){
//                    if(res.getString("状态")!=null && res.getString("状态").equals("0")){
//                        obj.put("reimbursementStatus", "1");//报销中
//                    }
//                }
//                obj.put("flowCode", nullToObject(res.getString("单据编号")));//报销单号
//                obj.put("id", res.getString("id"));
//                dataList.add(obj);
//            }
//            for (int i = 0; i < dataList.size(); i++) {
//                res = stmt.executeQuery("select field0176 as invoiceId,'0730' from formson_0730 where formmain_id = "+dataList.getJSONObject(i).getString("id"));
//                JSONArray invoiceList=new JSONArray();
//
//                while (res.next()) {
//                    if(res.getString("invoiceId")!=null){
//                        JSONObject obj=new JSONObject();
//                        obj.put("invoiceId", res.getString(1));
//                        invoiceList.add(obj);
//                    }
//                }
//                dataList.getJSONObject(i).put("invoiceList", invoiceList);
//
//                //金税票夹接口地址
//                String url=ip+"/billfold/inner/v1/invoice/operateReimbursementStatus";
//                JSONObject param=new JSONObject();
//                param.put("version", "1.0");
//                param.put("nonce", new Date().getTime());
//                param.put("timestamp", sf.format(new Date()));
//                param.put("data", dataList.getJSONObject(i).toJSONString().replaceAll("\"", "\\\""));
//
//                //调用接口批量更新发票报销状态
//                HttpClient httpclient = new HttpClient();
//                PostMethod method = new PostMethod(url);
//                method.getParams().setParameter(HttpMethodParams.HTTP_CONTENT_CHARSET,"utf-8");
//                method.setRequestHeader("Content-Type","application/json;charset=UTF-8");
//                RequestEntity se=new StringRequestEntity(param.toJSONString(),"application/json" ,"UTF-8");
//                method.setRequestEntity(se);
//                httpclient.executeMethod(method);
//                JSONObject obj = JSONObject.parseObject(method.getResponseBodyAsString());
//                System.out.println("formmain_0728单据编号:"+dataList.getJSONObject(i).getString("flowCode")+",状态:"+dataList.getJSONObject(i).getString("reimbursementStatus")+",结果:"+obj);
//            }
//        } catch (Exception e) {
//            // TODO: handle exception
//            e.printStackTrace();
//        }
//    }

//    public void tbBillState(String template,String summaryId,String reimbursementStatus){
//        System.out.println("同步状态进来啦！-------------------------------------------------------------");
//        if(template!=null
//                &&!template.equals("")
//                &&!template.equals("")
//                &&!template.equals("")
//        ){
//            return;
//        }
//        Connection conn=new DBConnection().getDefConn();
//        Statement stmt=null;
//        try {//20736
//            stmt=conn.createStatement();
//            ResultSet res;
//            SimpleDateFormat sf=new SimpleDateFormat("yyyyMMddHHmmss");
//            //出差
//            JSONArray dataList;
//            if(template.equals("")){
//                //查询formmain_0706 连接 org_unit.id = formmain_0706.field0321  连接 col_summary（协同表）.form_recordid = formmain_0706.id
//                //col_summary.id = 传进来的协同id
//                res=stmt.executeQuery("select m.id,field0059 员工工号,field0451 税号,u.name 所属单位,field0057 单据编号,s.state 状态 " +
//                        " from formmain_0706 m,org_unit u,col_summary s " +
//                        " where u.id=field0321 and s.form_recordid=m.id and s.id="+summaryId);
//                dataList=new JSONArray();
//                //循环查到的数据,将数据放入数组中
//                while (res.next()) {
//                    JSONObject obj=new JSONObject();
//                    obj.put("userId", res.getString("员工工号"));//用户编码
//                    obj.put("agencyCode", res.getString("税号"));//报销单位税号
//                    obj.put("agencyName", res.getString("所属单位"));//报销单位名称
//                    obj.put("reimbursementId", res.getString("单据编号"));//报销单号
//                    obj.put("reimbursementStatus", reimbursementStatus);
//                    obj.put("flowCode", nullToObject(res.getString("单据编号")));//报销单号
//                    obj.put("id", res.getString("id"));
//                    dataList.add(obj);
//                }
//                //循环查到的数据
//                for (int i = 0; i < dataList.size(); i++) {
//                    //查询formson_0709表 主表id字段 = id
//                    //查询formson_0710表 主表id字段 = id
//                    //查询formson_0711表 主表id字段 = id
//                    res = stmt.executeQuery("select field0432 as invoiceId,'a' from formson_0709 where formmain_id = "+dataList.getJSONObject(i).getString("id")+" " +
//                            " union select field0007 as invoiceId,'b' from formson_0710 where formmain_id = "+dataList.getJSONObject(i).getString("id")+" " +
//                            " union select field0433 as invoiceId,'c' from formson_0711 where formmain_id = "+dataList.getJSONObject(i).getString("id"));
//                    JSONArray invoiceList=new JSONArray();
//
//                    while (res.next()) {
//                        if(res.getString("invoiceId")!=null){
//                            JSONObject obj=new JSONObject();
//                            obj.put("invoiceId", res.getString(1));
//                            invoiceList.add(obj);
//                        }
//                    }
//                    dataList.getJSONObject(i).put("invoiceList", invoiceList);
//
//                    //金税票夹接口地址
//                    String url=ip+"/billfold/inner/v1/invoice/operateReimbursementStatus";
//                    JSONObject param=new JSONObject();
//                    param.put("version", "1.0");
//                    param.put("nonce", new Date().getTime());
//                    param.put("timestamp", sf.format(new Date()));
//                    param.put("data", dataList.getJSONObject(i).toJSONString().replaceAll("\"", "\\\""));
//
//                    //调用接口 更新发票报销状态
//                    HttpClient httpclient = new HttpClient();
//                    PostMethod method = new PostMethod(url);
//                    method.getParams().setParameter(HttpMethodParams.HTTP_CONTENT_CHARSET,"utf-8");
//                    method.setRequestHeader("Content-Type","application/json;charset=UTF-8");
//                    RequestEntity se=new StringRequestEntity(param.toJSONString(),"application/json" ,"UTF-8");
//                    method.setRequestEntity(se);
//                    httpclient.executeMethod(method);
//                    JSONObject obj = JSONObject.parseObject(method.getResponseBodyAsString());
//                    System.out.println("单据编号:"+dataList.getJSONObject(i).getString("flowCode")+",状态:"+dataList.getJSONObject(i).getString("reimbursementStatus")+",结果:"+obj);
//                }
//            }
//
//            //现金报销 同上
//            if(template.equals("")){
//                System.out.println("现金报销进来啦！-----------------------------------------------");
//                res=stmt.executeQuery("select m.id,field0068 员工工号,field0227 税号,u.name 所属单位,field0001 单据编号,s.state 状态 " +
//                        " from formmain_0722 m,org_unit u,col_summary s " +
//                        " where u.id=field0070 " +
//                        " and s.form_recordid=m.id " +
//                        " and s.id="+summaryId);
//                System.out.println("查询回来的数据{}" + res);
//                dataList=new JSONArray();
//                while (res.next()) {
//                    JSONObject obj=new JSONObject();
//                    obj.put("userId", res.getString("员工工号"));//用户编码
//                    obj.put("agencyCode", res.getString("税号"));//报销单位税号
//                    obj.put("agencyName", res.getString("所属单位"));//报销单位名称
//                    obj.put("reimbursementId", res.getString("单据编号"));//报销单号
//                    obj.put("reimbursementStatus", reimbursementStatus);
//                    obj.put("flowCode", nullToObject(res.getString("单据编号")));//报销单号
//                    obj.put("id", res.getString("id"));
//                    dataList.add(obj);
//                }
//                for (int i = 0; i < dataList.size(); i++) {
//                    res = stmt.executeQuery("select field0192 as invoiceId,'0723' from formson_0723 where formmain_id = "+dataList.getJSONObject(i).getString("id"));
//                    System.out.println("循环查询的sql语句{}:select field0192 as invoiceId,'0723' from formson_0723 where formmain_id = "+dataList.getJSONObject(i).getString("id"));
//                    System.out.println("循环sql查询到的数据{}" +  res);
//                    JSONArray invoiceList=new JSONArray();
//
//                    while (res.next()) {
//                        if(res.getString("invoiceId")!=null){
//                            JSONObject obj=new JSONObject();
//                            obj.put("invoiceId", res.getString(1));
//                            invoiceList.add(obj);
//                        }
//                    }
//                    dataList.getJSONObject(i).put("invoiceList", invoiceList);
//
//                    String url=ip+"/billfold/inner/v1/invoice/operateReimbursementStatus";
//                    JSONObject param=new JSONObject();
//                    param.put("version", "1.0");
//                    param.put("nonce", new Date().getTime());
//                    param.put("timestamp", sf.format(new Date()));
//                    param.put("data", dataList.getJSONObject(i).toJSONString().replaceAll("\"", "\\\""));
//
//                    HttpClient httpclient = new HttpClient();
//                    PostMethod method = new PostMethod(url);
//                    method.getParams().setParameter(HttpMethodParams.HTTP_CONTENT_CHARSET,"utf-8");
//                    method.setRequestHeader("Content-Type","application/json;charset=UTF-8");
//                    RequestEntity se=new StringRequestEntity(param.toJSONString(),"application/json" ,"UTF-8");
//                    method.setRequestEntity(se);
//                    httpclient.executeMethod(method);
//                    JSONObject obj = JSONObject.parseObject(method.getResponseBodyAsString());
//                    System.out.println("单据编号:"+dataList.getJSONObject(i).getString("flowCode")+",状态:"+dataList.getJSONObject(i).getString("reimbursementStatus")+",结果:"+obj);
//                }
//            }
//
//            //用款报销 同上
//            if(template.equals("")){
//                res=stmt.executeQuery("select m.id,field0068 员工工号,field0119 税号,u.name 所属单位,field0001 单据编号,s.state 状态 " +
//                        " from formmain_0728 m,org_unit u,col_summary s " +
//                        " where u.id=field0070 " +
//                        " and s.form_recordid=m.id " +
//                        " s.id="+summaryId);
//                dataList=new JSONArray();
//                while (res.next()) {
//                    JSONObject obj=new JSONObject();
//                    obj.put("userId", res.getString("员工工号"));//用户编码
//                    obj.put("agencyCode", res.getString("税号"));//报销单位税号
//                    obj.put("agencyName", res.getString("所属单位"));//报销单位名称
//                    obj.put("reimbursementId", res.getString("单据编号"));//报销单号
//                    obj.put("reimbursementStatus", reimbursementStatus);
//                    obj.put("flowCode", nullToObject(res.getString("单据编号")));//报销单号
//                    obj.put("id", res.getString("id"));
//                    dataList.add(obj);
//                }
//                for (int i = 0; i < dataList.size(); i++) {
//                    res = stmt.executeQuery("select field0176 as invoiceId,'0730' from formson_0730 where formmain_id = "+dataList.getJSONObject(i).getString("id"));
//                    JSONArray invoiceList=new JSONArray();
//
//                    while (res.next()) {
//                        if(res.getString("invoiceId")!=null){
//                            JSONObject obj=new JSONObject();
//                            obj.put("invoiceId", res.getString(1));
//                            invoiceList.add(obj);
//                        }
//                    }
//                    dataList.getJSONObject(i).put("invoiceList", invoiceList);
//
//                    String url=ip+"/billfold/inner/v1/invoice/operateReimbursementStatus";
//                    JSONObject param=new JSONObject();
//                    param.put("version", "1.0");
//                    param.put("nonce", new Date().getTime());
//                    param.put("timestamp", sf.format(new Date()));
//                    param.put("data", dataList.getJSONObject(i).toJSONString().replaceAll("\"", "\\\""));
//
//                    HttpClient httpclient = new HttpClient();
//                    PostMethod method = new PostMethod(url);
//                    method.getParams().setParameter(HttpMethodParams.HTTP_CONTENT_CHARSET,"utf-8");
//                    method.setRequestHeader("Content-Type","application/json;charset=UTF-8");
//                    RequestEntity se=new StringRequestEntity(param.toJSONString(),"application/json" ,"UTF-8");
//                    method.setRequestEntity(se);
//                    httpclient.executeMethod(method);
//                    JSONObject obj = JSONObject.parseObject(method.getResponseBodyAsString());
//                    System.out.println("单据编号:"+dataList.getJSONObject(i).getString("flowCode")+",状态:"+dataList.getJSONObject(i).getString("reimbursementStatus")+",结果:"+obj);
//                }
//            }
//        } catch (Exception e) {
//            // TODO: handle exception
//            e.printStackTrace();
//        }
//
//        System.out.println("同步发票状态结束啦！----------------------------------------------------------------------------");
//    }

    public String nullToObject(Object obj){
        if(obj==null)
            return "";
        else
            return obj+"";
    }
}
